﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using DataTable=System.Data.DataTable;
using Workbook=Microsoft.Office.Interop.Excel.Workbook;
using Worksheet=Microsoft.Office.Interop.Excel.Worksheet;

namespace Workflows.Components.ETL
{
	/// <summary>
	/// 
	/// </summary>
	public partial class ExcelReaderEditor : Form, IComponentEditor
	{
		/// <summary>
		/// 
		/// </summary>
		public ExcelReaderEditor()
		{
			InitializeComponent();
		}

		#region Implementation of IComponentEditor

		private object _ActivityNode;
		private ExcelReaderComponent _ExcelReaderComponent;

		public event UpdateComponentSettings UpdateComponentSettingEvent;

		/// <summary></summary>
		public object OwnerActivityNode
		{
			get { return _ActivityNode; }
			set { _ActivityNode = value; }
		}

		/// <summary></summary>
		public Type WorkflowComponentType
		{
			get { return typeof (ExcelReaderComponent); }
		}

		/// <summary></summary>
		public void LoadSettings(GlobalVariable[] globalVars, WorkflowComponentBase[] fromComponents, object activityNode, WorkflowComponentBase wfComponent)
		{
			this._ActivityNode = activityNode;
			this._ExcelReaderComponent = (ExcelReaderComponent) wfComponent;

			ExcelReaderInstruction instruction = (ExcelReaderInstruction) this._ExcelReaderComponent.Instruction;
			this.txt_ExcelFilePath.Text = instruction.ExcelFilePath;
			if (File.Exists(instruction.ExcelFilePath))
			{
			    List<string> sheetNames = ExcelReader.GetWorksheetNames(instruction.ExcelFilePath);
				this.cbo_Sheets1.Items.Clear();
				this.cbo_Sheets2.Items.Clear();
				if (sheetNames != null)
				{
					foreach (string worksheetName in sheetNames)
					{
						this.cbo_Sheets1.Items.Add(worksheetName);
						this.cbo_Sheets2.Items.Add(worksheetName);
					}
				}
			}
			this.txt_OutputFilePath.Text = instruction.OutputFilePath;
		    this.txt_ConnStr.Text = instruction.TgtConnStr;
		    this.txt_Schema.Text = instruction.TgtSchemaName;
		    this.txt_Table.Text = instruction.TgtTableName;
		    this.ck_ClearData.Checked = instruction.ClearExistingData;
		    this.ck_RecreateTable.Checked = instruction.RecreateTgtTable;
            if(!string.IsNullOrEmpty(instruction.OutputFilePath))
            {
                this.rd_Xml.Checked = true;
                this.rd_DB.Checked = false;
            }
            else if(!string.IsNullOrEmpty(instruction.TgtConnStr))
            {
                this.rd_Xml.Checked = false;
                this.rd_DB.Checked = true;
            }

			this.dv_ColDef.Rows.Clear();
			if(instruction.ColumnDataTypes !=null && instruction.ColumnDataTypes.Count>0)
			{
				foreach(string sheetName in instruction.ColumnDataTypes.Keys)
				{
					foreach(string colName in instruction.ColumnDataTypes[sheetName].Keys)
					{
						DataGridViewRow dr = new DataGridViewRow();
						DataGridViewTextBoxCell cellSheet = new DataGridViewTextBoxCell();
						cellSheet.Value = sheetName;
						dr.Cells.Add(cellSheet);
						DataGridViewTextBoxCell cellName = new DataGridViewTextBoxCell();
						cellName.Value = colName;
						dr.Cells.Add(cellName);
						DataGridViewComboBoxCell cellDbType = new DataGridViewComboBoxCell();
						foreach(SimpleDataType dbType in Enum.GetValues(typeof(SimpleDataType)))
						{
                            cellDbType.Items.Add(dbType.ToString());
						}
					    cellDbType.Value = instruction.ColumnDataTypes[sheetName][colName].ToString();
						dr.Cells.Add(cellDbType);
						DataGridViewTextBoxCell cellSize = new DataGridViewTextBoxCell();
                        if (instruction.ColumnSizes != null && instruction.ColumnSizes.ContainsKey(sheetName) &&
                            instruction.ColumnSizes[sheetName].ContainsKey(colName))
                            cellSize.Value = instruction.ColumnSizes[sheetName][colName];
                        else
                            cellSize.Value = 4;
                        dr.Cells.Add(cellSize);
						this.dv_ColDef.Rows.Add(dr);
					}
				}
			}
		}

		/// <summary></summary>
		public void SaveSettings()
		{
			ExcelReaderInstruction instruction =
				new ExcelReaderInstruction(
					this._ExcelReaderComponent.ComponentID, Guid.Empty, 
                    string.Empty, string.Empty,
                    new Dictionary<string, Dictionary<string, SimpleDataType>>(), 
                    new Dictionary<string, Dictionary<string, int>>(), 
                    string.Empty, string.Empty, string.Empty, true, true);

			instruction.ExcelFilePath = this.txt_ExcelFilePath.Text;
            if(this.rd_Xml.Checked)
            {
                instruction.OutputFilePath = this.txt_OutputFilePath.Text;    
            }
            else
            {
                instruction.TgtConnStr = this.txt_ConnStr.Text;
                instruction.TgtSchemaName = this.txt_Schema.Text;
                instruction.TgtTableName = this.txt_Table.Text;
                instruction.ClearExistingData = this.ck_ClearData.Checked;
                instruction.RecreateTgtTable = this.ck_RecreateTable.Checked;
            }
			if(this.dv_ColDef.Rows.Count>0)
			{
				foreach(DataGridViewRow dr in this.dv_ColDef.Rows)
				{
					if (!dr.IsNewRow)
					{
						string sheetName = (string) dr.Cells[0].Value;
						string colName = (string) dr.Cells[1].Value;
						SimpleDataType dbType = SimpleDataType.TextType;
						if (dr.Cells[2].Value != null)
							dbType = (SimpleDataType) Enum.Parse(typeof (SimpleDataType), (string) dr.Cells[2].Value);
					    int size = int.Parse(dr.Cells[3].Value.ToString());
                        if(instruction.ColumnDataTypes.ContainsKey(sheetName))
                        {
                            Dictionary<string, SimpleDataType> dbTypes = instruction.ColumnDataTypes[sheetName];
                            dbTypes.Add(colName, dbType);
                            instruction.ColumnDataTypes[sheetName] = dbTypes;
                            Dictionary<string, int> sizes = instruction.ColumnSizes[sheetName];
                            sizes.Add(colName, size);
                            instruction.ColumnSizes[sheetName] = sizes;
                        }
                        else
                        {
                            Dictionary<string,SimpleDataType> dbTypes=new Dictionary<string, SimpleDataType>();
                            dbTypes.Add(colName, dbType);
                            instruction.ColumnDataTypes.Add(sheetName, dbTypes);
                            Dictionary<string,int> sizes=new Dictionary<string, int>();
                            sizes.Add(colName, size);
                            instruction.ColumnSizes.Add(sheetName, sizes);
                        }
					}
				}
			}
			this._ExcelReaderComponent.Instruction = instruction;
		}

		/// <summary></summary>
		public TestResult Test()
		{
			throw new System.NotImplementedException();
		}

		#endregion

		private void btn_Browse_Click(object sender, EventArgs e)
		{
			this.cbo_Sheets1.Items.Clear();
			this.cbo_Sheets2.Items.Clear();
			OpenFileDialog dlg = new OpenFileDialog();
			dlg.Filter = "Excel files(*.xls;*.xlsx)|*.xls;*.xlsx|All files(*.*)|*.*";
			if (dlg.ShowDialog() == DialogResult.OK)
			{
				this.txt_ExcelFilePath.Text = dlg.FileName;
			    List<string> sheetNames = ExcelReader.GetWorksheetNames(dlg.FileName);
				if(sheetNames !=null && sheetNames.Count>0)
				{
					foreach(string worksheetName in sheetNames)
					{
						this.cbo_Sheets1.Items.Add(worksheetName);
						this.cbo_Sheets2.Items.Add(worksheetName);
					}
				}
			}
		}

		private void btn_Save_Click(object sender, EventArgs e)
		{
			SaveFileDialog dlg = new SaveFileDialog();
			dlg.Filter = "Xml files(*.xml)|*.xml|All files(*.*)|*.*";
			if(dlg.ShowDialog()==DialogResult.OK)
				this.txt_OutputFilePath.Text = dlg.FileName;
		}

		#region range
		/// <summary></summary>
		public Range GetCell(Worksheet sheet, int colIndex, int rowIndex)
		{
			return (Range)sheet.Cells[rowIndex, colIndex];
		}
		/// <summary></summary>
		public Range GetRange(Worksheet sheet, string name)
		{
			return this.GetCell(sheet, this.GetColumnIndex(name), this.GetRowIndex(name));
		}
		/// <summary></summary>
		public int GetColumnIndex(string name)
		{
			Regex charRegex = new Regex("(^[^\\d]+)\\d+$");
			Match charMatch = charRegex.Match(name);
			Char[] colChars = null;
			if (charMatch.Success)
			{
				colChars = charMatch.Groups[1].Value.ToCharArray();
			}
			else
			{
				colChars = name.ToCharArray();
			}

			int colIndex = 0;
			for (int i = 0; i < colChars.Length; i++)
			{
				int j = colChars.Length - i - 1;
				int k = 26 * i;
				char x = colChars[j];
				int m = this.Asc(x) - this.Asc('A') + 1;
				colIndex += k + m;
			}
			return colIndex;
		}
		/// <summary></summary>
		public string GetColumnName(int colIndex)
		{
			char left = ' ';
			char right = ' ';
			for (int i = 0; i <= 26; i++)
			{
				for (int k = 1; k <= 26; k++)
				{
					int index = i * 26 + k;
					if (index == colIndex)
					{
						if (i > 0)
						{
							left = this.Chr(64 + i);
						}
						right = this.Chr(64 + k);

						k = 100;
						i = 100;
						break;
					}
				}
			}
			return string.Concat(left.ToString(), right.ToString()).Trim();
		}
		/// <summary></summary>
		public int GetRowIndex(string name)
		{
			Regex charRegex = new Regex("(^[^\\d]+)(\\d+)$");
			Match charMatch = charRegex.Match(name);
			int rowIndex = int.Parse(charMatch.Groups[2].Value);
			return rowIndex;
		}
		/// <summary></summary>
		public int Asc(char c)
		{
			return (int)Encoding.ASCII.GetBytes(new char[] { c })[0];
		}
		/// <summary></summary>
		public char Chr(int i)
		{
			return Convert.ToChar(i);
		}
		#endregion

		private void cbo_Sheets2_SelectedIndexChanged(object sender, EventArgs e)
		{
            if (this.cbo_Sheets2.SelectedIndex >= 0)
            {
                try
                {
                    string sheetName = this.cbo_Sheets2.SelectedItem.ToString();
                    DataTable dt = ExcelReader.ReadTable(this.txt_ExcelFilePath.Text, sheetName);
                    if (dt != null)
                    {
                        this.dv_Preview.DataSource = dt;
                        this.dv_Preview.Refresh();
                    }
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
		}

		private void btn_Cancel_Click(object sender, EventArgs e)
		{
			this.Close();
		}

		private void btn_OK_Click(object sender, EventArgs e)
		{
			this.SaveSettings();
			if (this.UpdateComponentSettingEvent != null)
				this.UpdateComponentSettingEvent(this._ActivityNode, this._ExcelReaderComponent);
			this.Close();
		}

		private void ExcelReaderEditor_Load(object sender, EventArgs e)
		{

		}

        private void cbo_Sheets1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(this.cbo_Sheets1.SelectedIndex>=0)
            {
                string sheetName = this.cbo_Sheets1.SelectedItem.ToString();
                DataTable dt = ExcelReader.ReadTable(this.txt_ExcelFilePath.Text, sheetName);
                this.dv_ColDef.Rows.Clear();
                foreach(DataColumn col in dt.Columns)
                {
                    int colSize = 1;
                    SimpleDataType dataType = this.InferDataType(dt, col.ColumnName, ref colSize);
                    DataGridViewRow dr = new DataGridViewRow();
                    DataGridViewTextBoxCell cellSheet = new DataGridViewTextBoxCell();
                    cellSheet.Value = sheetName;
                    dr.Cells.Add(cellSheet);
                    DataGridViewTextBoxCell cellName = new DataGridViewTextBoxCell();
                    cellName.Value = col.ColumnName;
                    dr.Cells.Add(cellName);
                    DataGridViewComboBoxCell cellDbType = new DataGridViewComboBoxCell();
                    foreach(SimpleDataType simpleDataType in Enum.GetValues(typeof(SimpleDataType)))
                    {
                        cellDbType.Items.Add(simpleDataType.ToString());
                    }
                    cellDbType.Value = dataType.ToString();
                    dr.Cells.Add(cellDbType);
                    DataGridViewTextBoxCell cellSize = new DataGridViewTextBoxCell();
                    cellSize.Value = colSize;
                    dr.Cells.Add(cellSize);
                    this.dv_ColDef.Rows.Add(dr);
                }
            }
        }

        private SimpleDataType InferDataType(DataTable dt, string colName, ref int colSize)
        {
            SimpleDataType colDataType = SimpleDataType.TextType;
            colSize = 1;
            List<SimpleDataType> dataTypeOrder=new List<SimpleDataType>();
            dataTypeOrder.Add(SimpleDataType.YesNoType);
            dataTypeOrder.Add(SimpleDataType.IntNumberType);
            dataTypeOrder.Add(SimpleDataType.DecimalNumberType);
            dataTypeOrder.Add(SimpleDataType.DoubleNumberType);
            dataTypeOrder.Add(SimpleDataType.DateTimeType);
            dataTypeOrder.Add(SimpleDataType.GuidType);
            dataTypeOrder.Add(SimpleDataType.TextType);
            int sampleSize = 200;
            if(sampleSize > dt.Rows.Count)
                sampleSize = dt.Rows.Count;
            foreach (SimpleDataType dataType in dataTypeOrder)
            {
                bool canCast = true;
                for(int i=0;i<sampleSize;i++)
                {
                    DataRow dr = dt.Rows[i];
                    if (dr[colName] != null && dr[colName] != DBNull.Value)
                    {
                        switch(dataType)
                        {
                            case SimpleDataType.YesNoType:
                                if(dr[colName].ToString()=="0" ||
                                    dr[colName].ToString()=="1")
                                    break;
                                bool boolVal = false;
                                canCast = bool.TryParse(dr[colName].ToString(), out boolVal);
                                break;
                            case SimpleDataType.IntNumberType:
                                int intVal;
                                canCast = int.TryParse(dr[colName].ToString(), out intVal);
                                break;
                            case SimpleDataType.DecimalNumberType:
                                decimal decVal;
                                canCast = decimal.TryParse(dr[colName].ToString(), out decVal);
                                break;
                            case SimpleDataType.DoubleNumberType:
                                double dblVal;
                                canCast = double.TryParse(dr[colName].ToString(), out dblVal);
                                break;
                            case SimpleDataType.DateTimeType:
                                DateTime dateVal;
                                canCast = DateTime.TryParse(dr[colName].ToString(), out dateVal);
                                break;
                            case SimpleDataType.GuidType:
                                try
                                {
                                    new Guid(dr[colName].ToString());
                                }
                                catch
                                {
                                    canCast = false;
                                }
                                break;
                            default:
                                string val = dr[colName].ToString();
                                if(val.Length>colSize)
                                    colSize = val.Length;
                                break;
                        }
                    }
                    if(!canCast)
                        break;
                }

                if(canCast)
                    return dataType;
            }
            return colDataType;
        }

        private void rd_DB_CheckedChanged(object sender, EventArgs e)
        {
            if (this.rd_DB.Checked)
            {
                this.txt_OutputFilePath.Enabled = false;
                this.btn_Browse.Enabled = false;
                this.txt_ConnStr.Enabled = true;
                this.txt_Schema.Enabled = true;
                this.txt_Table.Enabled = true;
                this.ck_RecreateTable.Enabled = true;
                this.ck_ClearData.Enabled = true;
            }
            else
            {
                this.txt_OutputFilePath.Enabled = true;
                this.btn_Browse.Enabled = true;
                this.txt_ConnStr.Enabled = false;
                this.txt_Schema.Enabled = false;
                this.txt_Table.Enabled = false;
                this.ck_RecreateTable.Enabled = false;
                this.ck_ClearData.Enabled = false;
            }
        }

        private void rd_Xml_CheckedChanged(object sender, EventArgs e)
        {
            if (this.rd_DB.Checked)
            {
                this.txt_OutputFilePath.Enabled = false;
                this.btn_Browse.Enabled = false;
                this.txt_ConnStr.Enabled = true;
                this.txt_Schema.Enabled = true;
                this.txt_Table.Enabled = true;
                this.ck_RecreateTable.Enabled = true;
                this.ck_ClearData.Enabled = true;
            }
            else
            {
                this.txt_OutputFilePath.Enabled = true;
                this.btn_Browse.Enabled = true;
                this.txt_ConnStr.Enabled = false;
                this.txt_Schema.Enabled = false;
                this.txt_Table.Enabled = false;
                this.ck_RecreateTable.Enabled = false;
                this.ck_ClearData.Enabled = false;
            }
        }
	}
}
